# 导入pymysql
import pymysql

# 创建链接
con = pymysql.connect(host='127.0.0.1', user='root', password='123456', database='zhy', port=3306,charset="utf8")
# 创建游标对象
cursor = con.cursor()

# # 若已有数据表weather_year_db，则删除
# query = "drop table if exists weather_year_db;"  # 更改表明
# cursor.execute(query)

# 创建数据表，用刚才提取的列索引作为字段
# 根据自己要创建的表格更改sql语句
sql = '''
    create table if not exists zte_task (
    datetime DATETIME not null,
    inVol float(50) not null,
    outVol float(50) not null,
    interface varchar(50) not null,
    primary key(datetime)
    )
    default charset=utf8; 
'''

# 执行sql
try:
    cursor.execute(sql)
    con.commit()
    print("创建表成功")
except Exception as e:
    print(e)
    print("创建失败")
finally:
    cursor.close()
    con.close()

##############################################################################################
#写数据到sql数据表
csv_name = 'xxxx/weather_year.csv'
data = pd.read_csv(csv_name, encoding="utf-8")
data_3 = list(data.values)

# 在数据表中写入数据，因为数据是列表类型，把他转化为元组更符合sql语句
for i in data_3:  # 因为数据是迭代列表，所以用循环把数据提取出来
    data_6 = tuple(i)
    sql = """insert into weather_year_db values{}""".format(data_6)
    cursor.execute(sql)
    con.commit()

